import pymysql

from log import log
from model.TableFeild import TableFeild


class Aces:

    def __init__(self):
        self.log = log

    def connect(self, userName, pwd, host, port):
        self.db = pymysql.connect(
            host=host,  # 数据库主机地址
            user=userName,  # 数据库用户名
            passwd=pwd,  # 数据库密码
            port=port
        )
        self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)

    def close(self):
        self.cursor.close()
        self.db.close()

    def showdbs(self):
        sql = "show databases"
        self.log.info(sql)
        self.cursor.execute(sql)
        dl = self.cursor.fetchall()
        dbList = []
        for db in dl:
            dbList.append(db["Database"])
        return dbList

    def showtables(self):
        sql = "show tables"
        self.log.info(sql)
        self.cursor.execute(sql)
        dl = self.cursor.fetchall()
        dbList = []
        for db in dl:
            dbList.append(db["Tables_in_" + self.dbName])
        return dbList

    def use(self, dbName):
        sql = "use %s" % dbName
        self.dbName = dbName
        self.log.info(sql)
        self.cursor.execute(sql)

    def select(self, tableName, order={}, where={}, parm=[], limit=[]):
        showParm = "*" if parm.__len__() == 0 else "".join(
            [(parm[i] + ', ' if i < parm.__len__() - 1 else parm[i] + ' ') for i in range(parm.__len__())])
        orderK = list(order.keys())
        orderV = [order[k] for k in orderK]
        orderParm = '' if len(order) == 0 else ''.join(
            [(orderK[i] + ' ' + orderV[i] + ',' if i < len(orderK) - 1 else orderK[i] + ' ' + orderV[i] + ' ')
             for i in range(len(orderK))]
        )
        if orderParm.__len__() > 0:
            orderParm = 'order by ' + orderParm
        wherek = list(where.keys())
        wherev = tuple(where[k] for k in wherek)
        whereParm = ""
        for i in range(len(wherek)):
            whereParm += wherek[i] + ' = ' + '%s'
            if i < len(wherek) - 1:
                whereParm += ' and '
            else:
                whereParm += ' '
        if len(whereParm) > 0:
            whereParm = ' where ' + whereParm
        listp = ''
        if len(limit) == 1:
            listp = 'limit ' + str(limit[0])
        elif len(limit) == 2:
            listp = 'limit ' + str(limit[0]) + ',' + str(limit[1])

        sql = "select %s from %s %s %s %s" % (showParm, tableName, whereParm, orderParm, listp)
        self.log.info(sql, wherev)
        self.cursor.execute(sql, wherev)
        dl = self.cursor.fetchall()
        return list(dl)

    def createDb(self, dbName):
        sql = 'create database %s' % dbName
        self.log.info(sql)
        print(self.cursor.execute(sql))

    def createTable(self, name, tableFeild: [TableFeild]):
        fmsg = ''
        defaultcommits = []
        for i in range(len(tableFeild)):
            feild: TableFeild = tableFeild[i]
            fmsg += feild.field + ' ' + feild.type + ' ' + (
                '' if feild.canNull else 'NOT NULL ')
            if feild.default:
                fmsg += ' DEFAULT %s'
                defaultcommits.append(feild.default)
            if feild.extra:
                fmsg += ' comment %s '
                defaultcommits.append(feild.extra)
            if i < len(tableFeild) - 1:
                fmsg += ','
        sql = 'create table ' + name + ' ( ' + fmsg + ' )'
        self.log.info(sql, defaultcommits)
        self.cursor.execute(sql, tuple(defaultcommits))
        self.cursor.fetchall()

    def descTable(self, name):
        sql = 'desc ' + name
        self.log.info(sql)
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def showCreateTable(self,name):
        sql = 'show create table ' + name
        self.log.info(sql)
        self.cursor.execute(sql)
        return self.cursor.fetchall()
